package org.bp.util;

import java.io.FileOutputStream;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.bp.event.ExcelInsertDataEvent;

public class ExcelUtils {
	/**
	 * 写入excel
	 * @param excelPath 路径文件名
	 * @param firstRow  表头
	 * @param titleRow  列标题
	 * @param columnNum 列数
	 * @param event		数据
	 */
	public static void writer(String excelPath, String firstRow, String titleRow[],int columnNum ,ExcelInsertDataEvent event) {
		Workbook wb = null;
		String extName = FileUtils.getExtName(excelPath);
		// 创建工作文档对象
		if (extName.equals(".xls") || extName.equals(".xlsx")) {
			wb = new HSSFWorkbook();
		} else {
			throw new RuntimeException("文件格式不正确");
		}
		// 创建sheet对象
		Sheet sheet = (Sheet) wb.createSheet("sheet1");
		//设置样式
		CellStyle style = createStyle(wb);
		
		int rowNum = 0;
		// 添加表头
		rowNum = wirteHeader(firstRow,rowNum,columnNum,style,sheet);
		// 添加列标题
		rowNum =wirteSecondHeader(titleRow,rowNum,style,sheet);
		// 循环写入行数据
		event.insertData(sheet, rowNum);

		// 创建文件流
		OutputStream stream = null;
		try {
			stream = new FileOutputStream(excelPath);
			// 写入数据
			wb.write(stream);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭文件流
			StreamUtils.close(wb, stream);
		}
	}
	/**
	 * 表头
	 * @param firstRow 表头
	 * @param rowNum 行号
	 * @param columnNum 列数
	 * @param style 样式
	 * @param sheet sheet
	 * @return
	 */
	private static int wirteHeader(String firstRow,int rowNum,int columnNum, CellStyle style,Sheet sheet) {
		// 添加表头
		if (ObjectUtils.isNotNull(firstRow)) {
			Row row = sheet.createRow(rowNum++);
			Cell cell = row.createCell(0);
			row.setHeight((short) 540);
			cell.setCellValue(firstRow);
			cell.setCellStyle(style); // 样式，居中
			// 单元格合并
			// 四个参数分别是：起始行，起始列，结束行，结束列
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnNum - 1));
			sheet.autoSizeColumn(5200);
		}
		return rowNum;
	}

	/**
	 * 列标题
	 * @param titleRow 列名
	 * @param rowNum 行号
	 * @param style 样式
	 * @param sheet sheet
	 * @return
	 */
	private static int wirteSecondHeader(String[] titleRow, int rowNum,CellStyle style,Sheet sheet) {
		if (ObjectUtils.isNotNull(titleRow)) {
			Row row = sheet.createRow(rowNum++); // 创建下一行
			Cell cell = null;
			for (int i = 0; i < titleRow.length; i++) {
				cell = row.createCell(i);
				cell.setCellValue(titleRow[i]);
				cell.setCellStyle(style); // 样式，居中
				sheet.setColumnWidth(i, 20 * 256);
			}
			row.setHeight((short) 540);
		}
		return rowNum;
	}
	
	/**
	 * 创建样式
	 * @param wb
	 * @return
	 */
	private static CellStyle createStyle(Workbook wb) {
		// 设置样式
		CellStyle style = wb.createCellStyle(); // 样式对象
		// 设置单元格的背景颜色为淡蓝色
		style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
		style.setAlignment(HorizontalAlignment.CENTER);// 水平
		style.setWrapText(true);// 指定当单元格内容显示不下时自动换行
		// 字体
		Font font = wb.createFont();
		font.setBold(true);// 加粗
		font.setFontName("宋体");
		font.setFontHeight((short) 280);
		style.setFont(font);
		return style;
	}
}
